package com.file.controller;

import com.file.entity.Employee;
import com.file.service.EmployeeService;
import com.file.util.ExcelUtil;
import com.file.util.ImportExcelUtils;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("excel")
public class ExcelController {

    @Autowired
    private ImportExcelUtils importExcelUtils;

    @Autowired
    private EmployeeService employeeService;



    @RequestMapping("show")
    public String toExcel(){
        return "excel";
    }


    @PostMapping("upload")
    public String upload(HttpServletRequest request){

        MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest)request;

        InputStream inputStream =null;
        List<List<Object>> list = null;

        MultipartFile file = multipartHttpServletRequest.getFile("fileName");
        if(file.isEmpty()){
            return "文件不能为空";
        }

        try{
            inputStream = file.getInputStream();
            System.out.println("输出的文件为"+inputStream);
            System.out.println(file.getOriginalFilename());
            list = importExcelUtils.getBankListByExcel(inputStream,file.getOriginalFilename());
            System.out.println("输出list"+list.size());
            inputStream.close();

            for (int i = 0; i < list.size(); i++) {

                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
                SimpleDateFormat sdf_two = new SimpleDateFormat("yyyy-MM-dd");
                Date parseData = null;
                Date datetime = null;
                java.sql.Date  date_one = null;
                java.sql.Date  date_two = null;
                try {
                    parseData = sdf.parse(String.valueOf(list.get(i).get(3)));
                    datetime =  sdf_two.parse(String.valueOf(list.get(i).get(6)));
                    long time = parseData.getTime();
                    long time_two = datetime.getTime();
                    date_one = new java.sql.Date(time);
                    date_two = new java.sql.Date(time_two);
                } catch (ParseException e) {
                    e.printStackTrace();
                }

                String gender = String.valueOf(list.get(i).get(4));
                int genderNumber = gender == "男"? 0 : 1;

                Employee employee = new Employee();
                employee.seteName(String.valueOf(list.get(i).get(1)));
                employee.setNational(String.valueOf(list.get(i).get(2)));
                employee.setBirthDate(date_one);
                employee.setGender(genderNumber);
                employee.setNativePlace(String.valueOf(list.get(i).get(5)));
                employee.setInTime(date_two);
                employee.setSubordinateDepartments(String.valueOf(list.get(i).get(7)));
                employee.setJobDepartment(String.valueOf(list.get(i).get(8)));
                employee.setLevelPosition(String.valueOf(list.get(i).get(9)));
                employee.setIdentityNumber(String.valueOf(list.get(i).get(10)));
                employee.setIdentityAddress(String.valueOf(list.get(i).get(11)));
                employee.setMobilePhone(String.valueOf(list.get(i).get(12)));

                employeeService.insertEmp(employee);

            }
        }catch (IOException e){
            throw  new RuntimeException("添加数据异常");
        }
        return "excel";
    }


    /**
     * 下载excel
     */
    @GetMapping("get")
    public String getUpload(HttpServletResponse response){
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("员工表");

        List<Employee> allEmp = employeeService.findAllEmp();

        String exportName = "employee.xls";


        //设置第一行数据
        String[] headers = { "姓名", "民族", "出生年月", "员工性别", "籍贯", "入职时间", "所属部门", "工作部门", "级别", "身份证号码", "身份证地址", "手机号码"};

        //在excel表中添加表头
        HSSFRow row = sheet.createRow(0);

        for(int i=0; i<headers.length; i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        //在表中存放查询到的数据放入对应的列
        for (int i = 0; i < allEmp.size(); i++) {
            HSSFRow rows = sheet.createRow(rowNum);
                   rows.createCell(0).setCellValue(allEmp.get(i).geteName());
                    rows.createCell(1).setCellValue(allEmp.get(i).getNational());
                    rows.createCell(2).setCellValue(allEmp.get(i).getBirthDate());
                    rows.createCell(3).setCellValue(allEmp.get(i).getGender());
                    rows.createCell(4).setCellValue(allEmp.get(i).getNativePlace());
                    rows.createCell(5).setCellValue(allEmp.get(i).getInTime());
                    rows.createCell(6).setCellValue(allEmp.get(i).getSubordinateDepartments());
                    rows.createCell(7).setCellValue(allEmp.get(i).getJobDepartment());
                    rows.createCell(8).setCellValue(allEmp.get(i).getLevelPosition());
                    rows.createCell(9).setCellValue(allEmp.get(i).getIdentityNumber());
                    rows.createCell(10).setCellValue(allEmp.get(i).getIdentityAddress());
                    rows.createCell(11).setCellValue(allEmp.get(i).getMobilePhone());
            rowNum ++;
        }
        try{
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + exportName);
            response.flushBuffer();
            workbook.write(response.getOutputStream());
        }catch (IOException e){
            throw  new RuntimeException("插入数据异常");
        }


        return "excel";
    }


    @GetMapping("getUtil")
    public void getUtil(HttpServletResponse response){

        //设置第一行数据
        String[] headers = { "姓名", "民族", "出生年月", "员工性别", "籍贯", "入职时间", "所属部门", "工作部门", "级别", "身份证号码", "身份证地址", "手机号码"};

        String sheetName = "员工表";

        List<Employee> allEmp = employeeService.findAllEmp();
        List<Object> objectList = new ArrayList<>();
        for (Employee employee : allEmp) {
            Object o = new Employee();
            BeanUtils.copyProperties(employee, o);
            objectList.add(o);
        }

        String exportName = "employee.xls";
        ExcelUtil.export(sheetName, headers, objectList, response, exportName);

    }





}
